import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import PatternFill
import pandas as pd

# 定义源目录、目标文件路径和名单文件路径
source_directory = r'E:\1d'
target_file_path = r'E:\1.xlsx'
name_list_file_path = r'E:\1d\a名单.xlsx'

# 读取名单文件中的A列数据
name_list_df = pd.read_excel(name_list_file_path)
name_list = name_list_df.iloc[:, 0].tolist()

# 获取源目录中的所有文件名
file_names = os.listdir(source_directory)

# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
ws.title = "FileNames"

# 写入标题行
ws.append(["FileName", "MatchedName"])

# 遍历源目录中的所有文件并写入工作表
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

for filename in file_names:
    matched = False
    for name in name_list:
        if name in filename:
            ws.append([filename, name])
            # 设置B列单元格为绿色
            ws.cell(row=ws.max_row, column=2).fill = green_fill
            matched = True
            break
    if not matched:
        ws.append([filename, ""])

# 保存工作簿到目标文件路径
wb.save(target_file_path)

print(f"文件名已成功写入 {target_file_path}")